# -*- coding: utf-8 -*-
#coding = utf-8

'''
Created on June 3, 2013

@author: Tong Zhang

Mysql DataBase Manager include: the connection pool in the DBUtils PooledDB , the batching insert operation in the MySQLdb
'''

import time
import logging

import MySQLdb
from DBUtils.PooledDB import PooledDB

SLEEP_FOR_NEXT_TRY = 3

class DBManager:
    def __init__(self, host, user, passwd, db):
        
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.conn = None

    def connect(self):

        logger = logging.getLogger('et.kernel.tcp.DataBaseManage.connect')

        if self.conn:
            return

        while True:
            try:
                self.conn = PooledDB(MySQLdb, maxusage=10, db=self.db, host=self.host, user=self.user, passwd=self.passwd, charset='utf8')
                break
            except Exception, e:
                logger.error('Sleep %d seconds for next try.' % SLEEP_FOR_NEXT_TRY)
                time.sleep(SLEEP_FOR_NEXT_TRY)

    def disconnect(self):

        logger = logging.getLogger('et.kernel.tcp.DataBaseManage.disconnect')

        if not self.conn:
            return
        try:
            self.conn.close()
            self.conn = None

        except Exception, e:
            logger.error(e)

    def operateDB(self, type, *sqls):

        logger = logging.getLogger('et.kernel.tcp.DataBaseManage.operateDB')

        if not self.conn:
            self.connect()
        try:
            pool = self.conn.connection()
            cur = pool.cursor()
            result = []
            if type == 'get':
                if cur.execute(sqls[0]) > 0:
                    result = cur.fetchall()
                    cur.close()
                    pool.close()
                    return result
                else:
                    return []
            elif type == 'set':
                for sql in sqls:
                    cur.execute(sql)
                    result = cur.fetchall()
                    cur.close()
                    pool.commit()
                    pool.close()
                    return result
            else:
                for sql in sqls:
                    cur.execute(sql)
                    cur.close()
                    pool.commit()
                    pool.close()
                    return True

        except Exception, e:
            logger.error(e)
            return None

    def batch_insert(self,sql,tmp):

        logger = logging.getLogger('et.kernel.tcp.DataBaseManage.batch_insert')

        result = 0
        if not self.conn:
            self.connect()
        try:
            pool = self.conn.connection()
            cur = pool.cursor()
            result = cur.executemany(sql, tmp)
            cur.close()
            pool.commit()
            pool.close()
            return result

        except Exception, e:
            logger.error(e)
            return 0 

    def get_results(self,sql,cnt):

        logger = logging.getLogger('et.kernel.tcp.DataBaseManage.batch_insert')

        if not self.conn:
            self.connect()
        try:
            pool = self.conn.connection()
            cur = pool.cursor()
            result = []
            cur.execute(sql)
            count = 0
            while count < cnt:

                result.append(cur.fetchall())
                cur.nextset()
                count = count + 1

            cur.close()
            pool.commit()
            pool.close()
            return result

        except Exception, e:
            logger.error(e)
            return []
